package com.litbw.dbbackend;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.litbw.dbbackend.DBManager.FieldType;
import com.litbw.event.Contest.Entry;
import com.litbw.user.Draft;
import com.litbw.user.OverTheLimitException;
import com.litbw.user.User;
import com.litbw.user.UserRole;

/**
 * The DBUserAdapter is a convenience utility class which provides DBManager
 * functionality for use with User objects.
 * 
 * To change the database file (no need really) either set below or
 * programatically the variable DB_PATH.
 * 
 * To change the table name (no need really) either set below or programatically
 * the variable TABLE_NAME.
 * 
 * Methods ------------------------------------------------------ (NO NEED TO
 * CALL DBManager DIRECTLY ANYMORE! JUST USE THE METHODS BELOW)
 * 
 * boolean addNewUser(final User usr); boolean updateUser(final User usr);
 * boolean deleteUser(final User usr); User getUser(final String user_name);
 * User[] getUsers(final String[] user_names);
 * 
 * @author Michael Morris
 * @version Feb 16, 2013
 * 
 */
public class DBUserAdapter {

	/*
	 * public static void main(final String... the_args) {
	 * 
	 * try {
	 * 
	 * User usr = new Contestant("joe", "shmoe"); usr.setState("WA");
	 * usr.setPhone("253-3504019");
	 * 
	 * System.out.println(DBUserAdapter.addNewUser(usr));
	 * 
	 * } catch (Exception ex) {
	 * 
	 * System.out.println("exception: " + ex.getMessage());
	 * 
	 * }
	 * 
	 * }
	 */

	/** The database where all data will be saved for Users. **/
	public static String DB_PATH = "alldata.db";

	/** The table that will be used for Users. **/
	public static String TABLE_NAME = "users";

	/** The DBManager instance used to connect and run queries. **/
	private static DBManager _db_instance = null;

	/** Private constructor to prevent instantiation. */
	private DBUserAdapter() {
		// Do nothing...
	}

	/**
	 * Private method for setting up connection before a query/update.
	 * 
	 * @return True if the connection is fully setup and ready for use.
	 */
	private static boolean checkDatabaseConnection() {

		// Make sure the DBManager has an instance.
		if (_db_instance == null)
			_db_instance = new DBManager(DB_PATH);

		// Connect to the instance.
		boolean rtn = _db_instance.connect();

		// If connected attempt to make/or-not the table (doesn't override so
		// this is ok).
		if (rtn) {

			// Create field setup for table
			Map<String, FieldType> fields = new HashMap<String, FieldType>();
			fields.put("name", FieldType.TEXT);
			fields.put("email", FieldType.TEXT);
			fields.put("phone", FieldType.INTEGER);
			fields.put("address", FieldType.TEXT);
			fields.put("city", FieldType.TEXT);
			fields.put("state", FieldType.TEXT);
			fields.put("zip", FieldType.TEXT);
			fields.put("role", FieldType.INTEGER);
			fields.put("password", FieldType.TEXT);

			// Create table with above fields using the 'email' field as primary
			// key and NOT autoincrementing.
			_db_instance.createTable(TABLE_NAME, fields, "email", false);

		}

		return rtn;

	}

	/**
	 * A convenience method for disconnecting from the database.
	 */
	private static void disconnect() {
		if (_db_instance != null)
			_db_instance.disconnect();
	}

	/**
	 * Retrieve an SQL compatible String from the given object, or NULL if the
	 * object will not be converted correctly.
	 * 
	 * @param obj
	 *            The object to retrieve string for.
	 * @return An String which can be used in SQL statement or null.
	 */
	private static String sqlAcceptable(final Object obj) {

		if (obj == null) {
			return "NULL";
		} else if (obj instanceof UserRole) {
			return String.valueOf(((UserRole) obj).ordinal());
		} else if (obj instanceof Integer || obj instanceof Double
				|| obj instanceof Float) {
			return obj.toString();
		} else if (obj instanceof String) {
			return "'" + obj.toString() + "'";
		} else {
			throw new IllegalArgumentException(
					"invalid type to be inserted in to sql statement");
		}

	}

	/**
	 * Retrieve a value list from the specified user.
	 * 
	 * @param usr
	 *            The user object to build list of.
	 * @return A String containing the comma separated value list.
	 */
	private static String valueListFromUser(final User usr) {

		String rtn = null;

		if (usr != null) {

			final StringBuilder sb = new StringBuilder();

			sb.append(sqlAcceptable(usr.getName()));
			sb.append(", ");
			sb.append(sqlAcceptable(usr.getEmail()));
			sb.append(", ");
			sb.append(sqlAcceptable(usr.getPhone()));
			sb.append(", ");
			sb.append(sqlAcceptable(usr.getAddress()));
			sb.append(", ");
			sb.append(sqlAcceptable(usr.getCity()));
			sb.append(", ");
			sb.append(sqlAcceptable(usr.getState()));
			sb.append(", ");
			sb.append(sqlAcceptable(usr.getZip()));
			sb.append(", ");
			sb.append(sqlAcceptable(usr.getRole()));
			sb.append(", ");
			sb.append(sqlAcceptable(usr.getPassword()));

			rtn = sb.toString();

		}

		return rtn;

	}

	/**
	 * Retrieves a field/value pair list from the specified user.
	 * 
	 * @param usr
	 *            The user object to build list of.
	 * @return A String containing the comma separated field/value pair list.
	 */
	private static String fieldValuePairsFromUser(final User usr) {

		String rtn = null;

		if (usr != null) {

			final StringBuilder sb = new StringBuilder();

			sb.append("name=");
			sb.append(sqlAcceptable(usr.getName()));
			sb.append(", ");
			sb.append("email=");
			sb.append(sqlAcceptable(usr.getEmail()));
			sb.append(", ");
			sb.append("phone=");
			sb.append(sqlAcceptable(usr.getPhone()));
			sb.append(", ");
			sb.append("address=");
			sb.append(sqlAcceptable(usr.getAddress()));
			sb.append(", ");
			sb.append("city=");
			sb.append(sqlAcceptable(usr.getCity()));
			sb.append(", ");
			sb.append("state=");
			sb.append(sqlAcceptable(usr.getState()));
			sb.append(", ");
			sb.append("zip=");
			sb.append(sqlAcceptable(usr.getZip()));
			sb.append(", role=");
			sb.append(sqlAcceptable(usr.getRole()));
			sb.append(", password=");
			sb.append(sqlAcceptable(usr.getPassword()));

			rtn = sb.toString();

		}

		return rtn;

	}

	/**
	 * Add a new user to the database.
	 * 
	 * @param usr
	 *            The User object to save to the database.
	 * @return True if addition was successful, false otherwise.
	 */
	public static boolean addNewUser(final User usr) {

		boolean rtn = false;

		if (checkDatabaseConnection() && usr != null) {
			// Build the sql statement.
			final String sql = "INSERT into "
					+ TABLE_NAME
					+ " (name, email, phone, address, city, state, zip, role, password) values ("
					+ valueListFromUser(usr) + ")";
			// Run the statement
			if (_db_instance.runUpdate(sql) > 0)
				rtn = true;
			// Disconnect from database and change return status.
			disconnect();

		}

		return rtn;

	}

	/**
	 * Update a previously stored user's information, using the email as an
	 * indication on what user (in the database) to update.
	 * 
	 * @param usr
	 *            The user to update.
	 * @return True if the update was successful, false otherwise.
	 */
	public static boolean updateUser(final User usr) {

		boolean rtn = false;

		if (checkDatabaseConnection() && usr != null) {

			// Build the sql statement.
			final String sql = "UPDATE " + TABLE_NAME + " SET "
					+ fieldValuePairsFromUser(usr) + " WHERE email='"
					+ usr.getEmail() + "'";

			// Run the statement
			if (_db_instance.runUpdate(sql) > 0)
				rtn = true;

			// Disconnect from database and change return status.
			disconnect();

		}

		return rtn;

	}

	/**
	 * Delete a given user from the database, using the users email as the
	 * indication on what user to delete.
	 * 
	 * @param usr
	 * @return
	 */
	public static boolean deleteUser(final User usr) {

		boolean rtn = false;

		if (checkDatabaseConnection() && usr != null) {

			// Build the sql statement.
			final String sql = "DELETE FROM " + TABLE_NAME + " WHERE email='"
					+ usr.getEmail() + "'";

			// Run the statement
			if (_db_instance.runUpdate(sql) > 0)
				rtn = true;

			// Disconnect from database and change return status.
			disconnect();

		}

		return rtn;

	}

	/**
	 * 
	 * @param usr
	 * @return
	 */
	public static ArrayList<User> getAllUsers() {

		ArrayList<User> rtn = null;

		if (checkDatabaseConnection()) {

			// Build the sql statement.
			final String sql = "SELECT * FROM " + TABLE_NAME;

			// Run the statement

			final ResultSet rslt = _db_instance.runQuery(sql);

			// Parse results.
			try {
				if (rslt != null && rslt.next()) {
					rtn = new ArrayList<User>();
					while (rslt.next()) {
						final String names[] = rslt.getString("name").split(", ");

						User tmp = new User(rslt.getString("email"), rslt.getString("password"));
						tmp.setAddress(rslt.getString("address"));
						tmp.setCity(rslt.getString("city"));
						tmp.setPhone(rslt.getString("phone"));
						tmp.setState(rslt.getString("state"));
						tmp.setZip(rslt.getString("zip"));
						tmp.setRole(UserRole.roleFromOrdinal(rslt.getInt("role")));
						tmp.setName(names[1].trim(), names[0].trim());
						rtn.add(tmp);
					}
				}

			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		// Disconnect from database and change return status.
		disconnect();
		return rtn;
	}

	/**
	 * Retrieve a User object which coincide with the login info specified.
	 * 
	 * @param user_name
	 *            The users login username/email.
	 * @param password
	 *            The users login password.
	 * @param usePassword
	 *            Whether or not to retrieve user if password also matches.
	 * @return A User object if found, null otherwise.
	 * 
	 */
	public static User getUserFromUsernameAndPass(final String user_name,
			final String password, final boolean usePassword) {

		User rtn = null;

		if (checkDatabaseConnection()
				&& user_name != null
				&& !user_name.isEmpty()
				&& ((!usePassword) || (usePassword && password != null && !password
						.isEmpty()))) {

			// Build the sql statement.
			String sql = "SELECT * FROM " + TABLE_NAME + " WHERE email='"
					+ user_name + "'";
			if (usePassword)
				sql += " AND password='" + password + "'";

			final ResultSet rslt = _db_instance.runQuery(sql);

			// Parse results.
			try {
				if (rslt != null && rslt.next()) {

					final String names[] = rslt.getString("name").split(", ");

					rtn = new User(user_name, rslt.getString("password"));
					rtn.setAddress(rslt.getString("address"));
					rtn.setCity(rslt.getString("city"));
					rtn.setPhone(rslt.getString("phone"));
					rtn.setState(rslt.getString("state"));
					rtn.setZip(rslt.getString("zip"));
					rtn.setRole(UserRole.roleFromOrdinal(rslt.getInt("role")));
					rtn.setName(names[1].trim(), names[0].trim());

				}

			} catch (Exception ex) {
				ex.printStackTrace();
			}
			// Disconnect from database and change return status.
			disconnect();

		}

		return rtn;

	}

	/**
	 * Retrieve a User object from the data base given his/her username/email.
	 * 
	 * @param user_name
	 *            The username/email of the User's data to get.
	 * @return A User object if found, or null.
	 */
	public static User getUser(final String user_name) {
		return getUserFromUsernameAndPass(user_name, null, false);
	}

	/**
	 * Retrieve an array of User objects which coincide with the array of user
	 * names specified.
	 * 
	 * @param user_names
	 *            An array of String objects containing user's email.
	 * @return An array of FOUND users within the database, or null if none can
	 *         be found.
	 */
	public static User[] getUsers(final String[] user_names) {

		User[] users = null;

		if (user_names != null) {

			// Just because users name is in user_names[] does not
			// mean its in the database so we cannot build the
			// array size by simply user_names.length.
			final List<User> users_in_db = new ArrayList<User>();

			for (String un : user_names) {

				final User rtn = getUser(un);
				if (rtn != null)
					users_in_db.add(rtn);

			}

			if (!users_in_db.isEmpty())
				users = users_in_db.toArray(new User[0]);

		}

		return users;

	}

}
